package com.ld.hpfs.web.controller;

import java.io.File;
import java.io.FileOutputStream;
import java.io.FileWriter;
import java.io.OutputStreamWriter;
import java.sql.DriverManager;
import java.sql.Statement;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.HashMap;
import java.util.List;
import java.util.TimerTask;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Controller;

import com.ld.hpfs.common.utils.WbExcel;
import com.ld.hpfs.pojo.DbSourceOutting;
import com.ld.hpfs.pojo.ExtReturn;
import com.ld.hpfs.service.DbSourceOuttingService;
import com.ld.hpfs.service.ExportService;
import com.ld.hpfs.web.interseptor.WebConstants;
@Controller
public class ExportWriteBackTask extends TimerTask {
	@Autowired
	private ExportService exportService;
	@Autowired
    private DbSourceOuttingService dbouttingService;
	private HashMap<String,Object> param = new HashMap<String,Object>();
	private int i=1;
	@Override
	public void run() {
		try{
			HashMap<String,Object> exportPlan = exportService.getExportPlan();//任意得到一个公司可执行计划的信息
			if(exportPlan==null){
				
			}else{
				Integer COMPANY_ID= (Integer)exportPlan.get("COMPANY_ID");
				Integer RUN_FLAG= (Integer)exportPlan.get("RUN_FLAG");
				if(RUN_FLAG==1){
					param.put("companyId", COMPANY_ID);
					String temp="";
					
					List<HashMap<String,Object>> WayList= (List<HashMap<String,Object>>)exportService.getWriteBackWay(param);
					for(HashMap<String,Object> wayinfo : WayList){
						String WriteBackWay= (String)wayinfo.get("ACK_ERP_TYPE");
						String DOC_TYPE_CODE= (String)wayinfo.get("DOC_TYPE_CODE");//得到票据类型
						param.put("DOC_TYPE_CODE", DOC_TYPE_CODE);
						
						if(WriteBackWay.equalsIgnoreCase("EXCEL")){//导出excel
							List<HashMap<String,Object>> exportPlaninfo = exportService.getWriteBackListAll(param);//得到该公司的可回写的信息
							if(!exportPlaninfo.isEmpty()){
								temp = "test";
								exportService.setRunFlag(param);//将该公司的可执行标志位置为0
								param.put("START_TIME",new SimpleDateFormat("yyyyMMdd hh:mm:ss").format(new Date()));
								param.put("STOP_TIME", new SimpleDateFormat("yyyyMMdd hh:mm:ss").format(new Date()));
								param.put("TASK_STATUS", 3);
								String now = new SimpleDateFormat("yyyyMMddhhmmss").format(new Date());
								
								i = i++;
								now = now+i;
								String fileName = now+".xls";
								String path= "/TAX/ERP/EXCEL/"+fileName;
								
								if(DOC_TYPE_CODE.equalsIgnoreCase("N_VAT")){//如果是普通票
									param.put("TASK_DESC1", "<a href="+path+">download</a>");
								}else if(DOC_TYPE_CODE.equalsIgnoreCase("VAT")){
									param.put("TASK_DESC2", "<a href="+path+">download</a>");
								}else if(DOC_TYPE_CODE.equalsIgnoreCase("BT")){
									param.put("TASK_DESC3", "<a href="+path+">download</a>");
								}else if(DOC_TYPE_CODE.equalsIgnoreCase("E_INV")){
									param.put("TASK_DESC4", "<a href="+path+">download</a>");
								}else if(DOC_TYPE_CODE.equalsIgnoreCase("T_INV")){
									param.put("TASK_DESC5", "<a href="+path+">download</a>");
								}
								
//								param.put("TASK_DESC", "<a href="+path+">download</a>");				 
//								exportService.saveplanTaskinfo(param);//保存这次执行的任务信息	
								path= this.getClass().getResource("/").getPath();
								path = path.substring(0,path.indexOf("TAX"))+"TAX/ERP/EXCEL/";
								///E:/我的/apache-tomcat-6.0.36/wtpwebapps/TAX/WEB-INF/classes/
//								path.substring(0, endIndex);
								//path = Const.PROPERTIES.getProperty("import.server.exportTask", "c:\\upload");
								File file = new File(path);
								if(!file.exists()){
									file.mkdirs();
									file.createNewFile();
								}
								path += fileName;
								WbExcel.writerExcel(exportPlaninfo,path);
														
							}
						}else if(WriteBackWay.equalsIgnoreCase("TXT")){//导出txt
							ArrayList<HashMap<String,Object>> exportPlaninfo = exportService.getWriteBackListAllTXT(param);//得到该公司的可回写的信息
							if(!exportPlaninfo.isEmpty()){
								temp = "test";
								exportService.setRunFlag(param);//将该公司的可执行标志位置为0
								param.put("START_TIME",new SimpleDateFormat("yyyyMMdd hh:mm:ss").format(new Date()));
								param.put("STOP_TIME", new SimpleDateFormat("yyyyMMdd hh:mm:ss").format(new Date()));
								param.put("TASK_STATUS", 3);
								String now = new SimpleDateFormat("yyyyMMddhhmmss").format(new Date());
								
								i = i++;
								now = now+i;
								String fileName = now+".txt";
								String path= "/TAX/ERP/TXT/"+fileName;
								
								if(DOC_TYPE_CODE.equalsIgnoreCase("N_VAT")){//如果是普通票
									param.put("TASK_DESC1", "<a href="+path+">download</a>");
								}else if(DOC_TYPE_CODE.equalsIgnoreCase("VAT")){
									param.put("TASK_DESC2", "<a href="+path+">download</a>");
								}else if(DOC_TYPE_CODE.equalsIgnoreCase("BT")){
									param.put("TASK_DESC3", "<a href="+path+">download</a>");
								}else if(DOC_TYPE_CODE.equalsIgnoreCase("E_INV")){
									param.put("TASK_DESC4", "<a href="+path+">download</a>");
								}else if(DOC_TYPE_CODE.equalsIgnoreCase("T_INV")){
									param.put("TASK_DESC5", "<a href="+path+">download</a>");
								}
								
//								param.put("TASK_DESC", "<a href="+path+">download</a>");				 
//								exportService.saveplanTaskinfo(param);//保存这次执行的任务信息	
								path= this.getClass().getResource("/").getPath();
								path = path.substring(0,path.indexOf("TAX"))+"TAX/ERP/TXT/";
								///E:/我的/apache-tomcat-6.0.36/wtpwebapps/TAX/WEB-INF/classes/
//								path.substring(0, endIndex);
								//path = Const.PROPERTIES.getProperty("import.server.exportTask", "c:\\upload");
								
								File file = new File(path);
								if(!file.exists()){
									file.mkdirs();
									path += fileName;
									file = new File(path);
									file.createNewFile();
								}else{
									path += fileName;
									file = new File(path);
									file.createNewFile();
								}
								
								
								String data = WbExcel.FormateDownloadData(exportPlaninfo,exportPlaninfo.size());
//								byte[] bs = data.getBytes();
//								data = new String(bs, "GBK");
								FileOutputStream fos=new FileOutputStream(file);
								OutputStreamWriter osw=new OutputStreamWriter(fos,"utf-8");
								osw.write(data);
								osw.flush();
								osw.close();
								
							}

						}else{//导出数据源
							exportService.setRunFlag(param);//将该公司的可执行标志位置为0
							
							int CorrectId = exportService.getCorrectId(param);  //新增：验证当前公司是否有引用的导出数据源   2013.4.2
							param.put("START_TIME",new SimpleDateFormat("yyyyMMdd hh:mm:ss").format(new Date()));
							param.put("STOP_TIME", new SimpleDateFormat("yyyyMMdd hh:mm:ss").format(new Date()));
//							param.put("TASK_STATUS", 3);
							if(CorrectId == COMPANY_ID){
								List<HashMap<String,Object>> list = exportService.getWriteBackListAll(param);//得到该公司的可回写的信息
								int ERP_ID = dbouttingService.getDbOuttingERPID(COMPANY_ID);  //new 2013.4.28：先获取当前公司的ACK_ERP_ID号，然后根据此号找出ERP数据源								

						    	String message = "yes";
						            
						        
						        	
						            String driver = null;
						            java.sql.Connection dbConn = null;
						            Statement sta = null;
						            
						            try {
							        	DbSourceOutting outting = dbouttingService.getDbSourceOutting(ERP_ID);
							        	
							        	String sql_sources ="";
							        	


							            if (outting.getDs_type().equals("oracle")) {
							                driver = "oracle.jdbc.driver.OracleDriver";
							            } else // sqlserver
							            {
							                driver = "net.sourceforge.jtds.jdbc.Driver";
							            }

							            String connectString = outting.getDs_connect_str();
						                Class.forName(driver);

						                dbConn = DriverManager.getConnection(connectString, outting.getDs_username(), outting.getDs_password());

						                sta = dbConn.createStatement();
						                for (int i = 0; i < list.size(); i++) {
											HashMap<String, Object> beanMap = list.get(i);
											sql_sources = outting.getSql_str();  //原始SQL语句
											String[] sql_groups = sql_sources.split(";");//若有多条SQL语句，则使用";"隔开每条SQL语句并进行依次执行到数据库
											if(sql_groups.length < 1){ //如果split()失败，则默认将源SQL语句执行。
												sql_groups[0] = sql_sources;
											}
											int flag =0;
											for(int gi=0;gi<sql_groups.length;gi++){  
												String sql = sql_groups[gi];//每条SQL语句
												int pos = 0;  //定位"{}"
									        	int cnt2 = 0; //定位"{}"
									        	if(flag==0){
										    		while (pos < sql.length()) {  //替换"{字段名}"模块
										    			pos = sql.indexOf("{", pos);
										    			cnt2 = sql.indexOf("}", pos);
										    			if (pos == -1) {
										    				break;
										    			} else {
										    				String sub2 = sql.substring(pos+1, cnt2); //被"{}"括起，需要替换值的字段，格式为：字段名
										    				
										    				cnt2++;
										    				String sub = sql.substring(pos, cnt2); //包含在"{}"的需要替换的字段，格式为：{字段名}
										    				
										    				if(beanMap.get(sub2) == null){ //与后台获取的单据比较，若不存在字段，则退出
										    					message = "字段{"+sub2+"}不存在";
										    					
										    					param.put("TASK_STATUS", 4);
														    	if(DOC_TYPE_CODE.equalsIgnoreCase("N_VAT")){//如果是普通票
																	param.put("TASK_DESC1", message);
																}else if(DOC_TYPE_CODE.equalsIgnoreCase("VAT")){
																	param.put("TASK_DESC2", message);
																}else if(DOC_TYPE_CODE.equalsIgnoreCase("BT")){
																	param.put("TASK_DESC3", message);
																}else if(DOC_TYPE_CODE.equalsIgnoreCase("E_INV")){
																	param.put("TASK_DESC4", message);
																}else if(DOC_TYPE_CODE.equalsIgnoreCase("T_INV")){
																	param.put("TASK_DESC5", message);
																}
														    	flag = 1;
														    	message = "failed";
										    					break;
										    					
										    				}
										    				message = "yes";
										    				sql =sql.replace(sub, "'"+beanMap.get(sub2).toString()+"'"); //替换后的完整字段
	//									    				System.out.println(sql);
	//									    				sta.execute(sql);
										    			}
										    		}
										    		if(flag==1){
										    			break;
										    		}
										    		sta.execute(sql);  //执行该SQL语句
									        	}
	
											}//小for
											if(flag==1){
								    			break;
								    		}
						                }//大for
						            } catch (Exception e) {
						                e.printStackTrace();
						                message = "failed";
						            } finally {
						                try {
						                    if (sta != null) {
						                        sta.close();
						                    }

						                    if (dbConn != null) {
						                        dbConn.close();
						                    }
						                } catch (Exception ex) {
						                	
						                }
						            }
						            
						        
						    
								
						    	if("yes".equals(message)){//运行成功
						    		param.put("TASK_STATUS", 3);
						        }else {//运行失败
									param.put("TASK_STATUS", 4);									
								}
						    	if(DOC_TYPE_CODE.equalsIgnoreCase("N_VAT")){//如果是普通票
									param.put("TASK_DESC1", message);
								}else if(DOC_TYPE_CODE.equalsIgnoreCase("VAT")){
									param.put("TASK_DESC2", message);
								}else if(DOC_TYPE_CODE.equalsIgnoreCase("BT")){
									param.put("TASK_DESC3", message);
								}else if(DOC_TYPE_CODE.equalsIgnoreCase("E_INV")){
									param.put("TASK_DESC4", message);
								}else if(DOC_TYPE_CODE.equalsIgnoreCase("T_INV")){
									param.put("TASK_DESC5", message);
								}
							} else{//当前公司无引用的导出数据源
								param.put("TASK_STATUS", 4);
								if(DOC_TYPE_CODE.equalsIgnoreCase("N_VAT")){//如果是普通票
									param.put("TASK_DESC1", "当前公司无引用的导出数据源");
								} else if(DOC_TYPE_CODE.equalsIgnoreCase("VAT")){
									param.put("TASK_DESC2", "当前公司无引用的导出数据源");
								} else if(DOC_TYPE_CODE.equalsIgnoreCase("BT")){
									param.put("TASK_DESC3", "当前公司无引用的导出数据源");
								} else if(DOC_TYPE_CODE.equalsIgnoreCase("E_INV")){
									param.put("TASK_DESC4", "当前公司无引用的导出数据源");
								} else if(DOC_TYPE_CODE.equalsIgnoreCase("T_INV")){
									param.put("TASK_DESC5", "当前公司无引用的导出数据源");
								}

							}

							
						}
						
					}
//					if(temp.equalsIgnoreCase("test") ){
						exportService.saveplanTaskinfo(param);//保存这次执行的任务信息
						param.clear();
//					}

				}
			}
			
			
		} catch (Exception e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}

	}

}
